package com.lora.web;

import com.acooly.core.common.dao.support.PageInfo;
import com.acooly.core.common.web.AbstractJQueryEntityController;
import com.acooly.core.common.web.support.JsonListResult;
import com.acooly.core.utils.Dates;
import com.acooly.core.utils.Servlets;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Maps;
import com.lora.entity.*;
import com.lora.enums.EquipmentEnum;
import com.lora.service.ElectricityMeterService;
import com.lora.service.EquipmentService;
import com.lora.service.UpsService;
import com.lora.util.StringUtil;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import org.apache.catalina.servlet4preview.http.HttpServletRequest;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.Boolean;
import java.util.*;

@Controller
@RequestMapping(value = "/manage/demo/report")
public class ReportFromController  extends AbstractJQueryEntityController {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private EquipmentService equipmentService;

    static List<String> equ=new ArrayList<>();
    static {
        equ.add("ffffff100000f368");//操作间
        equ.add("ffffff100000ef87");//1号顶
        equ.add("ffffff100000f2d0");//9号顶
        equ.add("ffffff100000f37b");//2
        equ.add("ffffff100000f2cf");//4
        equ.add("ffffff100000f343");//6
        equ.add("ffffff100000f308");//8
        equ.add("ffffff100000f36c");//10
    }

    @RequestMapping("towirelessSmokeFeel")
    public String towirelessSmokeFeel(HttpServletRequest request) {
        return "/manage/report/wirelessSmokeFeel";
    }

    @ResponseBody
    @RequestMapping("wirelessSmokeFeelReport")
    public JsonListResult<Map<String, Object>> wirelessSmokeFeelReport(HttpServletRequest request) {
        JsonListResult<Map<String, Object>> result = new JsonListResult<>();
        String type = request.getParameter("type");
        String begin = request.getParameter("begin");
        String end = request.getParameter("end");
        String sqlwhere = "";
        String fromat = "DATE_FORMAT(a.create_time,'%Y-%m-%d')";
        if (!StringUtils.isBlank(type)) {
            if (type.equals("1")) {
                fromat = "DATE_FORMAT(a.create_time,'%Y-%m-%d')";
            } else if (type.equals("1")) {
                fromat = "DATE_FORMAT(a.create_time,'%Y-%m')";
            } else {
                fromat = "DATE_FORMAT(a.create_time,'%Y')";
            }
        }
        if (!StringUtils.isBlank(begin)) {
            sqlwhere += " and a.create_time>='" + begin + "' ";
        }
        if (!StringUtils.isBlank(end)) {
            sqlwhere += " and a.create_time<='" + end + "' ";
        }
        String sql = "SELECT " + fromat + " as createTime," +
                " case when (sum(case  when (equipmentid='ffffff100000648f' and alarm_flag='1') then 1 else 0 end)>0)then '×' else '√' end as a," +
                " case when (sum(case  when (equipmentid='ffffff1000006672' and alarm_flag='1') then 1 else 0 end)>0)then '×' else '√' end as b," +
                " case when (sum(case  when (equipmentid='ffffff1000006808' and alarm_flag='1') then 1 else 0 end)>0)then '×' else '√' end as c," +
                " case when (sum(case  when (equipmentid='ffffff100000631d' and alarm_flag='1') then 1 else 0 end)>0)then '×' else '√' end as d," +
                " case when (sum(case  when (equipmentid='ffffff10000069f6' and alarm_flag='1') then 1 else 0 end)>0)then '×' else '√' end as e " +
                " from wireless_smoke_feel a where 1=1 " + sqlwhere + " group by " + fromat +"  ORDER BY createTime desc ";


//        String sql="SELECT "+fromat+" as createTime," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='1号机柜',1,0)) as t1," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='2号机柜',1,0)) as t2," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='3号机柜',1,0)) as t3," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='4号机柜',1,0)) as t4," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='5号机柜',1,0)) as t5," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='6号机柜',1,0)) as t6," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='7号机柜',1,0)) as t7," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='8号机柜',1,0)) as t8," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='9号机柜',1,0)) as t9," +
//                "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='10号机柜',1,0)) as t10" +
//                " from wireless_smoke_feel a LEFT JOIN equipent e on a.equipmentid=e.eid where 1=1 "+sqlwhere+" group by "+fromat;
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        result.setRows(maps);
        return result;
    }

    @RequestMapping(value = "wirelessSmokeFeelExport")
    public void wirelessSmokeFeelExport(HttpServletRequest request, HttpServletResponse response, Model model) {
        try {
            String[] fields = {"t1", "t2", "t3", "t4", "t5", "t6", "t7", "t8", "t9", "t10"};
            String[] ptitles = {"1号机柜", "2号机柜", "3号机柜", "4号机柜", "5号机柜", "6号机柜", "7号机柜", "8号机柜", "9号机柜", "10号机柜"};
            String type = request.getParameter("type");
            String begin = request.getParameter("begin");
            String end = request.getParameter("end");
            String sqlwhere = "";
            String fromat = "DATE_FORMAT(a.create_time,'%Y-%m-%d')";
            if (!StringUtils.isBlank(type)) {
                if (type.equals("1")) {
                    fromat = "DATE_FORMAT(a.create_time,'%Y-%m-%d')";
                } else if (type.equals("1")) {
                    fromat = "DATE_FORMAT(a.create_time,'%Y-%m')";
                } else {
                    fromat = "DATE_FORMAT(a.create_time,'%Y')";
                }
            }
            if (!StringUtils.isBlank(begin)) {
                sqlwhere += " and a.create_time>='" + begin + "' ";
            }
            if (!StringUtils.isBlank(end)) {
                sqlwhere += " and a.create_time>='" + end + "' ";
            }
            String sql = "SELECT " + fromat + " as createTime," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='1号机柜',1,0)) as t1," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='2号机柜',1,0)) as t2," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='3号机柜',1,0)) as t3," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='4号机柜',1,0)) as t4," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='5号机柜',1,0)) as t5," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='6号机柜',1,0)) as t6," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='7号机柜',1,0)) as t7," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='8号机柜',1,0)) as t8," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='9号机柜',1,0)) as t9," +
                    "sum(if((a.state=020100||a.state=020200||a.state=020300)&&e.position='10号机柜',1,0)) as t10" +
                    " from wireless_smoke_feel a LEFT JOIN equipent e on a.equipmentid=e.eid where 1=1 " + sqlwhere + " group by " + fromat;
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            //List<Map<String, Object>> list = jdbcTemplate.queryForList(sb.toString());

            // 输出文件头
            doExportExcelHeader(request, response);
            WritableWorkbook workbook = null;
            OutputStream out = null;
            try {
                out = response.getOutputStream();
                workbook = Workbook.createWorkbook(out);
                WritableSheet sheet = workbook.createSheet(getExportFileName(request), 0);
                // 设置单元格格式
                WritableCellFormat headerFormat = new WritableCellFormat(new WritableCellFormat());
                // 水平居中
                headerFormat.setAlignment(Alignment.CENTRE);
                // 上下居中
                headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
                String[] titles = ptitles;
                // 行号
                int row = 0;
                int colunmSize = titles.length;
                WritableFont font = new WritableFont(WritableFont.ARIAL, 20);
                // 设置单元格格式
                WritableCellFormat headerFormatTitle = new WritableCellFormat(new WritableCellFormat());
                // 水平居中
                headerFormatTitle.setAlignment(Alignment.CENTRE);
                headerFormatTitle.setFont(font);
                // 报表头
                // sheet.addCell(new Label(0, row, "客户信息", headerFormatTitle));
                // 合并单元格，参数格式（开始列，开始行，结束列，结束行）
                // sheet.mergeCells(0, 0, colunmSize - 1, 0);
                row++;
                WritableCellFormat headerFormat1 = new WritableCellFormat(new WritableCellFormat());
                // 水平局右
                headerFormat1.setAlignment(Alignment.RIGHT);
                // 统计时间
                sheet.addCell(new Label(0, row - 1, "回访满意度报表", headerFormat1));
                sheet.addCell(new Label(0, row, "导出时间：" + Dates.format(new Date()), headerFormat1));
                sheet.mergeCells(0, 1, colunmSize - 1, 1);
                row++;
                // 写入header
                String[] hs = titles;
                for (int i = 0; i < hs.length; i++) {
                    sheet.addCell(new Label(i, row, hs[i], headerFormat));
                }
                // 换行
                row++;
                for (Map map1 : list) {
                    int i = 0;
                    for (String str : fields) {
                        String v = BaseUtil.isEmpty(map1.get(str)) ? "" : map1.get(str).toString();

                        sheet.addCell(new Label(i++, row, v, headerFormat));
                    }
                    row++;
                }
                workbook.write();
                out.flush();
            } catch (Exception e1) {
                e1.printStackTrace();
                throw new RuntimeException("执行导出过程失败[" + e1.getMessage() + "]");
            } finally {
                try {
                    workbook.close();
                } catch (Exception e2) {
                    e2.printStackTrace();
                }
                IOUtils.closeQuietly(out);
            }
        } catch (Exception e) {
            e.printStackTrace();
            handleException("导出Excel", e, request);
        }

    }

    @RequestMapping("toSwitchingValue")
    public String toSwitchingValue(HttpServletRequest request) {
        return "/manage/report/SwitchingValue";
    }

    @ResponseBody
    @RequestMapping("SwitchingValueReport")
    public JsonListResult<Map<String, Object>> SwitchingValueReport(HttpServletRequest request) {
        JsonListResult<Map<String, Object>> result = new JsonListResult<>();
        String type = request.getParameter("type");
        String begin = request.getParameter("begin");
        String end = request.getParameter("end");
        String sqlwhere = "";
        String fromat = "DATE_FORMAT(a.create_time,'%Y-%m-%d')";
        if (!StringUtils.isBlank(type)) {
            if (type.equals("1")) {
                fromat = "DATE_FORMAT(a.create_time,'%Y-%m-%d')";
            } else if (type.equals("1")) {
                fromat = "DATE_FORMAT(a.create_time,'%Y-%m')";
            } else {
                fromat = "DATE_FORMAT(a.create_time,'%Y')";
            }
        }
        if (!StringUtils.isBlank(begin)) {
            sqlwhere += " and a.create_time>='" + begin + "' ";
        }
        if (!StringUtils.isBlank(end)) {
            sqlwhere += " and a.create_time<='" + end + "' ";
        }
        String sql = "SELECT " + fromat + " as createTime," +
                " case when (sum(case when (equipmentid='ffffff100000f0f9' and di1!=0) then 1 else 0 end)>0)then '×' else '√' end  as a1," +
                " case when (sum(case when (equipmentid='ffffff100000f0f9' and di2!=0) then 1 else 0 end)>0)then '×' else '√' end  as a2," +
                " case when (sum(case when (equipmentid='ffffff100000f0f9' and di3!=1) then 1 else 0 end)>0)then '×' else '√' end  as a3," +
                " case when (sum(case when (equipmentid='ffffff100000f10f' and di1!=0) then 1 else 0 end)>0)then '×' else '√' end  as a4," +
                " case when (sum(case when (equipmentid='ffffff100000f10f' and di2!=0) then 1 else 0 end)>0)then '×' else '√' end  as a5," +
                " case when (sum(case when (equipmentid='ffffff100000f10f' and di3!=0) then 1 else 0 end)>0)then '×' else '√' end  as a6," +
                " case when (sum(case when (equipmentid='ffffff100000f10f' and di4!=0) then 1 else 0 end)>0)then '×' else '√' end  as a7," +
                " case when (sum(case when (equipmentid='ffffff100000f10f' and di5!=0) then 1 else 0 end)>0)then '×' else '√' end  as a8," +
                " case when (sum(case when (equipmentid='ffffff100000f10f' and di6!=0) then 1 else 0 end)>0)then '×' else '√' end  as a9," +
                " case when (sum(case when (equipmentid='ffffff100000f10f' and di7!=0) then 1 else 0 end)>0)then '×' else '√' end  as a10," +
                " case when (sum(case when (equipmentid='ffffff100000f10f' and di8!=0) then 1 else 0 end)>0)then '×' else '√' end  as a11" +
                " from lora_switching_value a where 1=1 " + sqlwhere + " group by " + fromat+" ORDER  BY createTime desc";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        result.setRows(maps);
        return result;
    }

    @RequestMapping("toLeakWater")
    public String toLeakWater(HttpServletRequest request) {
        return "/manage/report/LeakWater";
    }

    @ResponseBody
    @RequestMapping("toLeakWaterReport")
    public JsonListResult<Map<String, Object>> toLeakWaterReport(HttpServletRequest request) {
        JsonListResult<Map<String, Object>> result = new JsonListResult<>();
        String type = request.getParameter("type");
        String begin = request.getParameter("begin");
        String end = request.getParameter("end");
        String sqlwhere = "";
        String fromat = "DATE_FORMAT(a.create_time,'%Y-%m-%d')";
        if (!StringUtils.isBlank(type)) {
            if (type.equals("1")) {
                fromat = "DATE_FORMAT(a.create_time,'%Y-%m-%d')";
            } else if (type.equals("1")) {
                fromat = "DATE_FORMAT(a.create_time,'%Y-%m')";
            } else {
                fromat = "DATE_FORMAT(a.create_time,'%Y')";
            }
        }
        if (!StringUtils.isBlank(begin)) {
            sqlwhere += " and a.create_time>='" + begin + "' ";
        }
        if (!StringUtils.isBlank(end)) {
            sqlwhere += " and a.create_time<='" + end + "' ";
        }
        String sql = "SELECT " + fromat + " as createTime," +
                    " case when (sum(case when (equipmentid='ffffff100000f0f9' and di5!=0) then 1 else 0 end)>0)then '×' else '√' end  as a1, "+
                    " case when (sum(case when (equipmentid='ffffff100000f0f9' and di6!=0) then 1 else 0 end)>0)then '×' else '√' end  as a2 "+
                " from lora_switching_value a where 1=1 " + sqlwhere + " group by " + fromat+" ORDER BY createTime desc";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        result.setRows(maps);
        return result;
    }


    @RequestMapping("totemperatureHumidity")
    public String totemperatureHumidity(HttpServletRequest request){
        return "/manage/report/temperatureHumidity";
    }

    @ResponseBody
    @RequestMapping("temperatureHumidityReport")
    public JsonListResult<Map<String,Object>> temperatureHumidityReport(HttpServletRequest request){
        JsonListResult<Map<String,Object>> result=new JsonListResult<>();
        String type=request.getParameter("type");
        String begin=request.getParameter("begin");
        String end=request.getParameter("end");
        if((begin==null||begin.trim().equals(""))&&(end==null||end.trim().equals(""))){
            begin = StringUtil.getMonth()+"-01";
            end =  StringUtil.getMonth()+"-31";
        }

        String sqlwhere="";
        String dataformat="'%Y-%m-%d'";
        String fromat="DATE_FORMAT(a.create_time,'%Y-%m-%d')";
        if(!StringUtils.isBlank(type)){
            if(type.equals("1")){
                fromat="DATE_FORMAT(a.create_time,'%Y-%m-%d')";
                dataformat="'%Y-%m-%d'";
            }else if(type.equals("1")){
                fromat="DATE_FORMAT(a.create_time,'%Y-%m')";
                dataformat="'%Y-%m'";
            }else {
                fromat="DATE_FORMAT(a.create_time,'%Y')";
                dataformat="'%Y'";
            }
        }

        if(!StringUtils.isBlank(begin)){
            sqlwhere+=" and create_time>='"+begin+"' ";
        }else{
            begin=getNowMonth()+"-01";
            sqlwhere+=" and create_time>='"+begin+"' ";
        }
        if(!StringUtils.isBlank(end)){
            sqlwhere+=" and create_time<='"+end+"' ";
        }else{
            end=getNowMonth()+"-31";
            sqlwhere+=" and create_time<='"+end+"' ";
        }
        String timesql="select \n" +
                "DATE_FORMAT(create_time,"+dataformat+") as time\n" +
                "from temperature_humidity\n" +
                "where 1=1 "+sqlwhere+
                "GROUP BY DATE_FORMAT(create_time,"+dataformat+") ORDER BY time desc ";

        List<Map<String, Object>> maps = jdbcTemplate.queryForList(timesql);
        List<Map<String, Object>> results = new ArrayList<>();
        if(maps!=null&&maps.size()>0){
            for (Map<String, Object> map : maps) {
                String time = map.get("time").toString();
                Map<String, Object> resultmap=new HashMap<>();
                resultmap.put("time",time);
                for (int i = 0; i < equ.size(); i++) {
                    String sql="SELECT\n" +
                            "CONCAT( min(s.temperature), \"-\", max( s.temperature )) as data\n" +
                            "FROM\n" +
                            "temperature_humidity s\n" +
                            "RIGHT JOIN equipent e ON s.equipmentid = e.eid \n" +
                            "WHERE\n" +
                            "DATE_FORMAT(s.create_time,"+dataformat+") = '"+time+"'\n" +
                            "AND e.eid = \""+equ.get(i)+"\"";
                    List<Map<String, Object>> data = jdbcTemplate.queryForList(sql);
                    if(data.get(0).get("data")==null){
                        resultmap.put("t"+(i+1),"0.00-0.00");
                    }else{
                        resultmap.put("t"+(i+1),data.get(0).get("data"));
                    }
                    sql="SELECT\n" +
                            "CONCAT( min(s.humidity), \"-\", max( s.humidity )) as data\n" +
                            "FROM\n" +
                            "temperature_humidity s\n" +
                            "RIGHT JOIN equipent e ON s.equipmentid = e.eid \n" +
                            "WHERE\n" +
                            "DATE_FORMAT(s.create_time,"+dataformat+") = '"+time+"'\n" +
                            "AND e.eid = \""+equ.get(i)+"\"";
                    data = jdbcTemplate.queryForList(sql);
                    if(data.get(0).get("data")==null){
                        resultmap.put("s"+(i+1),"0.00-0.00");
                    }else{
                        resultmap.put("s"+(i+1),data.get(0).get("data"));
                    }
                }
                results.add(resultmap);
            }
        }
        result.setRows(results);
        return result;
    }

    /**
     * 新风
     * @param request
     * @return
     */
    @RequestMapping("tofreshAirUps")
    public String tofreshAirUps(HttpServletRequest request){
        return "/manage/report/freshAirUps";
    }

    @ResponseBody
    @RequestMapping("freshAirUpsreport")
    public JsonListResult<Map<String,Object>> freshAirUpsReport(HttpServletRequest request){
        JsonListResult<Map<String,Object>> result=new JsonListResult<>();
        String type=request.getParameter("type");
        String begin=request.getParameter("begin");
        String end=request.getParameter("end");
        String sqlwhere="";
        String dataformat="'%Y-%m-%d'";
        String fromat="DATE_FORMAT(create_time,'%Y-%m-%d')";
        int index=1;
        if(!StringUtils.isBlank(type)){
            if(type.equals("1")){
                fromat="DATE_FORMAT(create_time,'%Y-%m-%d')";
                dataformat="'%Y-%m-%d'";
            }else if(type.equals("2")){
                index=2;
                fromat="DATE_FORMAT(create_time,'%Y-%m')";
                dataformat="'%Y-%m'";
            }else {
                index=3;
                fromat="DATE_FORMAT(create_time,'%Y')";
                dataformat="'%Y'";
            }
        }
        if(StringUtils.isBlank(begin)){
            begin=getNowMonth()+"-01 00:00:00";
        }
        switch (index){
            case 1:begin=begin.substring(0,10);break;
            case 2:begin=begin.substring(0,7);break;
            case 3:begin=begin.substring(0,4);break;
        }
        sqlwhere+=" and l.t1>='"+begin+"' ";

        if(StringUtils.isBlank(end)){
            end=getNowDate()+" 59:00:00";
        }
        sqlwhere+=" and l.t1<='"+end+"' ";

        String sql="SELECT\n" +
                "t1,\n" +
                "t2,\n" +
                "if(t2='关机','-',t3) as t3,\n" +
                "if(t2='关机','-',t4) as t4,\n" +
                "if(t2='关机','-',t5) as t5,\n" +
                "if(t2='关机','-',t6) as t6,\n" +
                "if(t2='关机','-',t7) as t7,\n" +
                "if(t2='关机','-',t8) as t8\n" +
                "FROM\n" +
                "(\n" +
                "SELECT\n" +
                ""+fromat+" AS t1,\n" +
                "(case s.switchingstate  \n" +
                "WHEN 0 THEN  \"关机\"\n" +
                "WHEN 1 THEN  \"开机\"\n" +
                "END\n" +
                ") as t2,\n" +
                "(case s.workingmode \n" +
                "WHEN 1 THEN  \"自动\"\n" +
                "WHEN 2 THEN  \"手动\"\n" +
                "WHEN 3 THEN  \"定时\"\n" +
                "END\n" +
                ") AS t3,"+
                "s.windspeed AS t4,\n" +
                "s.equipmentid AS equipmentid \n" +
                "FROM\n" +
                "fresh_air_ups s\n" +
                "RIGHT JOIN ( SELECT max( create_time ) AS time FROM fresh_air_ups GROUP BY DATE_FORMAT( create_time, "+dataformat+" ) ) t ON s.create_time = t.time \n" +
                ") l\n" +
                "LEFT JOIN (\n" +
                "SELECT\n" +
                ""+fromat+" AS time,\n" +
                "CONCAT( min( pm25 ), \"~\", max( pm25 ) ) AS t5,\n" +
                "CONCAT( min( pm10 ), \"~\", max( pm10 ) ) AS t6,\n" +
                "CONCAT( min( temperature ), \"~\", max( temperature ) ) AS t7,\n" +
                "CONCAT( min( humidity ), \"~\", max( humidity ) ) AS t8 \n" +
                "FROM\n" +
                "fresh_air_ups \n" +
                "GROUP BY\n" +
                "DATE_FORMAT( create_time, "+dataformat+" ) \n" +
                ") d ON l.t1 = d.time \n" +
                "WHERE 1=1 "+sqlwhere+" ORDER BY t1 desc";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        result.setRows(maps);
        return result;
    }
    /**
     * 空调
     */
    @RequestMapping("toairConditioningUps")
    public String  toairConditioningUps(HttpServletRequest request){
        return "manage/report/airConditioningUps";
    }

    @ResponseBody
    @RequestMapping("airConditioningUpsReport")
    public JsonListResult<Map<String,Object>> airConditioningUpsReport(HttpServletRequest request){
        JsonListResult<Map<String,Object>> result=new JsonListResult<>();
        String type=request.getParameter("type");
        String begin=request.getParameter("begin");
        String end=request.getParameter("end");
        String eid=request.getParameter("eid");
        String sqlwhere="";
        String dataformat="'%Y-%m-%d'";
        String fromat="DATE_FORMAT(create_time,'%Y-%m-%d')";
        int index=1;
        if(!StringUtils.isBlank(type)){
            if(type.equals("1")){
                fromat="DATE_FORMAT(create_time,'%Y-%m-%d')";
                dataformat="'%Y-%m-%d'";
            }else if(type.equals("2")){
                index=2;
                fromat="DATE_FORMAT(create_time,'%Y-%m')";
                dataformat="'%Y-%m'";
            }else {
                index=3;
                fromat="DATE_FORMAT(create_time,'%Y')";
                dataformat="'%Y'";
            }
        }
        if(StringUtils.isBlank(begin)){
            begin=getNowMonth()+"-01 00:00:00";
        }
        switch (index){
            case 1:begin=begin.substring(0,10);break;
            case 2:begin=begin.substring(0,7);break;
            case 3:begin=begin.substring(0,4);break;
        }
        sqlwhere+=" and l.time>='"+begin+"' ";

        if(StringUtils.isBlank(end)){
            end=getNowDate()+" 59:00:00";
        }
        sqlwhere+=" and l.time<='"+end+"' ";
        if(eid==null||eid.trim().equals("")){
            eid="ffffff100000f110";
        }
        String sql="SELECT\n" +
                "DATE_FORMAT( l.time,"+dataformat+") AS time,\n" +
                "(\n" +
                "CASE\n" +
                "s.airconditioningrunstate \n" +
                "WHEN 0 THEN\n" +
                "\"本地关机\" \n" +
                "WHEN 1 THEN\n" +
                "\"远程关机\" \n" +
                "WHEN 2 THEN\n" +
                "\"监控关机\" \n" +
                "WHEN 3 THEN\n" +
                "\"组网待机\" \n" +
                "WHEN 4 THEN\n" +
                "\"电源保护\" \n" +
                "WHEN 5 THEN\n" +
                "\"气流保护\" \n" +
                "WHEN 6 THEN\n" +
                "\"压机锁定\" \n" +
                "WHEN 7 THEN\n" +
                "\"系统开机\" \n" +
                "END \n" +
                ") AS t1,\n" +
                "if(airconditioningrunstate in (0,1,2),\"-\",s.refrigerationsettingpoint )AS t2,\n" +
                "if(airconditioningrunstate in (0,1,2),\"-\",CONCAT(s.humiditysettingpoint*10,'%')) AS t3,\n" +
                "if(airconditioningrunstate in (0,1,2),\"-\",s.returnairtemperaturenumber) AS t4,\n" +
                "if(airconditioningrunstate in (0,1,2),\"-\",s.returnairtemperaturepercentage) AS t5,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\",CASE s.refrigerationstate WHEN 0 THEN \"-\" WHEN 1 THEN \"√\" END ) ) AS t6,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\", CASE s.heatingstate WHEN 0 THEN \"-\" WHEN 1 THEN \"√\" END )) AS t7,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\", CASE s.humidificationstate WHEN 0 THEN \"-\" WHEN 1 THEN \"√\" END )) AS t8,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\", CASE s.fehumidificationstate WHEN 0 THEN \"-\" WHEN 1 THEN \"√\" END )) AS t9,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\", CASE s.airsupplytemperaturefailure WHEN 0 THEN \"√\" WHEN 1 THEN \"×\" END )) AS t10,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\", CASE s.hightemperaturealarm WHEN 0 THEN \"√\" WHEN 1 THEN \"×\" END )) AS t11,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\", CASE s.lowtemperaturealarm WHEN 0 THEN \"√\" WHEN 1 THEN \"×\" END )) AS t12,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\",CASE s.highhumidityalarm WHEN 0 THEN \"√\" WHEN 1 THEN \"×\" END )) AS t13,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\", CASE s.lowhumidityalarm WHEN 0 THEN \"√\" WHEN 1 THEN \"×\" END )) AS t14,\n" +
                "(if(airconditioningrunstate in (0,1,2),\"-\", CASE s.flooroverflow WHEN 0 THEN \"√\" WHEN 1 THEN \"×\" END ) )AS t15\n" +
                "FROM\n" +
                "air_conditioning_ups s\n" +
                "RIGHT JOIN ( \n" +
                "SELECT equipmentid, max( create_time ) AS time FROM air_conditioning_ups WHERE equipmentid = \""+eid+"\" and airconditioningrunstate is not null\n" +
                "GROUP BY DATE_FORMAT(create_time,"+dataformat+") ) l ON s.create_time = l.time \n" +
                "AND s.equipmentid = l.equipmentid where 1=1  "+sqlwhere+" ORDER BY time desc";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        result.setRows(maps);
        return result;
    }

    /**
     * 市电量
     * @param request
     * @return
     */
    @RequestMapping("toelectricityMeter")
    public String toelectricityMeter(HttpServletRequest request){
        return "/manage/report/electricityMeter";
    }
    /**
     * ups电量仪
     * @param request
     * @return
     */
    @RequestMapping("toelectricityMeterups")
    public String toelectricityMeterups(HttpServletRequest request){
        return "/manage/report/electricityMeterups";
    }
    /**
     * ups
     * @param request
     * @return
     */
    @RequestMapping("toups")
    public String toups(HttpServletRequest request){
        return "/manage/report/ups";
    }
    /**
     * 全部设备状态年报
     * @param request
     * @return
     */
    @RequestMapping("toequipentAllN")
    public String toequipentAllN(HttpServletRequest request){
        return "/manage/report/equipentAllN";
    }
    /**
     * 全部设备状态月报
     * @param request
     * @return
     */
    @RequestMapping("toequipentAllY")
    public String toequipentAllY(HttpServletRequest request){
        return "/manage/report/equipentAllY";
    }
    /**
     * 全部设备状态周报
     * @param request
     * @return
     */
    @RequestMapping("toequipentAllZ")
    public String toequipentAllZ(HttpServletRequest request){
        return "/manage/report/equipentAllZ";
    }
    /**
     * 全部设备状态日报
     * @param request
     * @return
     */
    @RequestMapping("toequipentAllR")
    public String toequipentAllR(HttpServletRequest request){
        return "/manage/report/equipentAllR";
    }


    @ResponseBody
    @RequestMapping("electricityMeterreport")
    public JsonListResult<Map<String,Object>> electricityMeterreport(HttpServletRequest request){
        JsonListResult<Map<String,Object>> result=new JsonListResult<>();
        String type=request.getParameter("type");
        String eid=request.getParameter("eid");
        String begin=request.getParameter("begin");
        String end=request.getParameter("end");
        String sqlwhere="";
        String fromat="DATE_FORMAT(a.create_time,'%Y-%m-%d')";
        if(!StringUtils.isBlank(type)){
            if(type.equals("1")){
                fromat="DATE_FORMAT(a.create_time,'%Y-%m-%d')";
            }else if(type.equals("2")){
                fromat="DATE_FORMAT(a.create_time,'%Y-%m')";
            }else {
                fromat="DATE_FORMAT(a.create_time,'%Y')";
            }
        }
        if(!StringUtils.isBlank(eid)){
            sqlwhere+=" and a.equipmentid='"+eid+"' ";
        }
        if(!StringUtils.isBlank(begin)){
            sqlwhere+=" and a.create_time>='"+begin+"' ";
        }
        if(!StringUtils.isBlank(end)){
            sqlwhere+=" and a.create_time<='"+end+"' ";
        }
        String s="SELECT\n" +
                " DATE_FORMAT(a.create_time,'%Y-%m-%d') as createTime," +
                " a_voltage as aVoltage, " +
                " b_voltage as bVoltage, " +
                " c_voltage as cVoltage, " +
                " a_current as aCurrent, " +
                " b_current as bCurrent, " +
                " c_current as cCurrent, " +
                " a_available_power as aAvailablePower, " +
                " b_available_power as bAvailablePower, " +
                " c_available_power as cAvailablePower, " +
                " rate as rate " +
                "FROM (SELECT*from lora_electricity_meter ORDER BY create_time) a  where 1=1 "+sqlwhere+
                " GROUP BY "+fromat+" ORDER BY createTime desc";
        String sql="SELECT "+fromat+"  as createTime, " +
                " if(sum(if(a_voltage<210||a_voltage>250,1,0))>0,'X','√') as aVoltage, " +
                " if(sum(if(b_voltage<210||b_voltage>250,1,0))>0,'X','√') as bVoltage, " +
                " if(sum(if(c_voltage<210||c_voltage>250,1,0))>0,'X','√') as cVoltage, " +
                " if(sum(if(a_current<1||a_current>20,1,0))>0,'X','√') as aCurrent, " +
                " if(sum(if(b_current<1||b_current>20,1,0))>0,'X','√') as bCurrent, " +
                " if(sum(if(c_current<1||c_current>20,1,0))>0,'X','√') as cCurrent, " +
                " if(sum(if(a_available_power<1,1,0))>0,'X','√') as aAvailablePower, " +
                " if(sum(if(b_available_power<1,1,0))>0,'X','√') as bAvailablePower, " +
                " if(sum(if(c_available_power<1,1,0))>0,'X','√') as cAvailablePower, " +
                " if(sum(if(rate<45||rate>55,1,0))>0,'X','√') as rate " +
                " from lora_electricity_meter a where 1=1 "+sqlwhere+
                " GROUP BY "+fromat+" ORDER BY createTime desc";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(s);
        result.setRows(maps);
        return result;
    }

    @ResponseBody
    @RequestMapping("upsreport")
    public JsonListResult<Map<String,Object>> upsreport(HttpServletRequest request){
        JsonListResult<Map<String,Object>> result=new JsonListResult<>();
        String type=request.getParameter("type");
        String eid=request.getParameter("eid");
        String begin=request.getParameter("begin");
        String end=request.getParameter("end");
        String sqlwhere="";
        String fromat="DATE_FORMAT(a.create_time,'%Y-%m-%d')";
        if(!StringUtils.isBlank(type)){
            if(type.equals("1")){
                fromat="DATE_FORMAT(a.create_time,'%Y-%m-%d')";
            }else if(type.equals("2")){
                fromat="DATE_FORMAT(a.create_time,'%Y-%m')";
            }else {
                fromat="DATE_FORMAT(a.create_time,'%Y')";
            }
        }
        if(!StringUtils.isBlank(eid)){
            sqlwhere+=" and a.equipmentid='"+eid+"' ";
        }
        if(!StringUtils.isBlank(begin)){
            sqlwhere+=" and a.create_time>='"+begin+"' ";
        }
        if(!StringUtils.isBlank(end)){
            sqlwhere+=" and a.create_time<='"+end+"' ";
        }
        String s="SELECT " +
                fromat+ " as createTime," +
                "ac_voltagea as acVoltagea, " +
                "ac_voltageb as acVoltageb, " +
                "ac_voltagec as acVoltagec, " +
                "ac_in_currenta as acCurrenta, " +
                "ac_in_currentb as acCurrentb, " +
                "ac_in_currentc as acCurrentc, " +
                "ac_out_voltagea as acOutVoltagea, " +
                "ac_out_voltageb as acOutVoltageb, " +
                "ac_out_voltagec as acOutVoltagec, " +
                "ac_out_currenta as acOutCurrenta, " +
                "ac_out_currentb as acOutCurrentb, " +
                "ac_out_currentc as acOutCurrentc, " +
                "out_look_powera as outLookPowera, " +
                "out_look_powera as outLookPowerb, " +
                "out_look_powera as outLookPowerc, " +
                "positive_battery_voltage as positiveBatteryVoltage, " +
                "battery_temperature as batteryTemperature, " +
                "battery_remain_capacity as batteryRemainCapacity " +
                "FROM (SELECT*from lora_ups ORDER BY create_time) a where 1=1 "+sqlwhere+
                " GROUP BY "+fromat+" ORDER BY createTime desc ";
        String sql="SELECT " +fromat+
                " as createTime, " +
                "if(sum(if(ac_voltagea>250||ac_voltagea<210,1,0))>0,'X','√') as acVoltagea, " +
                "if(sum(if(ac_voltageb>250||ac_voltageb<210,1,0))>0,'X','√') as acVoltageb, " +
                "if(sum(if(ac_voltagec>250||ac_voltagec<210,1,0))>0,'X','√') as acVoltagec, " +
                "if(sum(if(ac_in_currenta>20||ac_in_currenta<1,1,0))>0,'X','√') as acCurrenta, " +
                "if(sum(if(ac_in_currentb>20||ac_in_currentb<1,1,0))>0,'X','√') as acCurrentb, " +
                "if(sum(if(ac_in_currentc>20||ac_in_currentc<1,1,0))>0,'X','√') as acCurrentc, " +
                "if(sum(if(ac_out_voltagea>250||ac_out_voltagea<210,1,0))>0,'X','√') as acOutVoltagea, " +
                "if(sum(if(ac_out_voltageb>250||ac_out_voltageb<210,1,0))>0,'X','√') as acOutVoltageb, " +
                "if(sum(if(ac_out_voltagec>250||ac_out_voltagec<210,1,0))>0,'X','√') as acOutVoltagec, " +
                "if(sum(if(ac_out_currenta>20||ac_out_currenta<1,1,0))>0,'X','√') as acOutCurrenta, " +
                "if(sum(if(ac_out_currentb>20||ac_out_currentb<1,1,0))>0,'X','√') as acOutCurrentb, " +
                "if(sum(if(ac_out_currentc>20||ac_out_currentc<1,1,0))>0,'X','√') as acOutCurrentc, " +
                "if(sum(if(out_look_powera>0.1||out_look_powera<2,1,0))>0,'X','√') as outLookPowera, " +
                "if(sum(if(out_look_powera>0.1||out_look_powera<2,1,0))>0,'X','√') as outLookPowerb, " +
                "if(sum(if(out_look_powera>0.1||out_look_powera<2,1,0))>0,'X','√') as outLookPowerc, " +
                "if(sum(if(positive_battery_voltage>250||positive_battery_voltage<210,1,0))>0,'X','√') as positiveBatteryVoltage, " +
                "if(sum(if(battery_temperature>35||battery_temperature<24,1,0))>0,'X','√') as batteryTemperature, " +
                "if(sum(if(battery_remain_capacity<100,1,0))>0,'X','√') as batteryRemainCapacity " +
                "from lora_ups a where 1=1 "+sqlwhere+" GROUP BY "+fromat+" ORDER BY createTime desc";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(s);
        result.setRows(maps);
        return result;
    }

    @ResponseBody
    @RequestMapping("equipentAll")
    public JsonListResult<Map<String,Object>> equipentAll(HttpServletRequest request){
        JsonListResult<Map<String,Object>> result=new JsonListResult<>();
        String type=request.getParameter("type");
        String eid=request.getParameter("eid");
        String begin=request.getParameter("begin");
        String end=request.getParameter("end");
        String sqlwhere="";
        String fromat="DATE_FORMAT(create_time,'%Y-%m-%d')";
        if(!StringUtils.isBlank(type)){
            if(type.equals("1")){
                fromat="DATE_FORMAT(create_time,'%Y-%m-%d')";
            }else if(type.equals("2")){
                fromat="DATE_FORMAT(create_time,'%Y-%m')";
            }else if(type.equals("3")){
                fromat="DATE_FORMAT(create_time,'%x年%v周')";
            } else {
                fromat="DATE_FORMAT(create_time,'%Y')";
            }
        }
        if(!StringUtils.isBlank(eid)){
            sqlwhere+=" and equipmentid='"+eid+"' ";
        }
        if(!StringUtils.isBlank(begin)){
            sqlwhere+=" and create_time>='"+begin+"' ";
        }
        if(!StringUtils.isBlank(end)){
            sqlwhere+=" and create_time<='"+end+"' ";
        }
        String sql="SELECT a.createTime,if(c.id is null,'√', 'x') as dly," +
                "if(airconditioningrunstate1 is null ,'√',airconditioningrunstate1) as airconditioningrunstate1," +
                "if(airconditioningrunstate2 is null ,'√',airconditioningrunstate2) as airconditioningrunstate2,if(di3 is null ,'√',di3) as di3," +
                "IF (e.id IS NULL, '√', 'x') AS ups," +
                "if(h.id is null ,'√','x') as temperature," +
                "if(xingfeng is null ,'√',xingfeng) as xingfeng,if(j.id is null, '√', 'x') as menci," +
                "if(k.id is null ,'√','x') as hongwai,if(yangan is null ,'√',yangan) as yangan,if(f.id is null ,'√','x') as jigui," +
                "if(sxt is null ,'√',sxt) as sxt" +
                " from (" +
                "SELECT case  when airconditioningrunstate2='-' then '-'  when airconditioningrunstate2='√' and b.id is null then '√' else 'x' end as airconditioningrunstate2, \n" +
                "a.createTime\n" +
                " from (SELECT\tIF (sum(IF (airconditioningrunstate = 7,1,0)) > 0,'√','-') AS airconditioningrunstate2,\n" +
                " "+fromat+" AS createTime\n" +
                "FROM\tair_conditioning_ups WHERE\t1 = 1 AND equipmentid = 'ffffff100000f10d'\n" +sqlwhere+
                " GROUP BY "+fromat+")a LEFT JOIN (\n" +
                "SELECT "+fromat+" AS createTime,id from lora_alarm_list \n" +
                "WHERE ep_id='ffffff100000f10d'\n" +sqlwhere+
                " GROUP BY "+fromat+" ) \n" +
                "b on a.createTime=b.createTime" +
                ") a LEFT JOIN ( " +
                "SELECT case  when airconditioningrunstate1='-' then '-'  when airconditioningrunstate1='√' and b.id is null then '√' else 'x' end as airconditioningrunstate1,\n" +
                "a.createTime\n" +
                " from (SELECT IF (sum(IF (airconditioningrunstate = 7,1,0)) > 0,'√','-') AS airconditioningrunstate1,\n" +
                " "+fromat+" AS createTime\n" +
                "FROM air_conditioning_ups WHERE\t1 = 1 AND equipmentid = 'ffffff100000f110'\n" +sqlwhere+
                " GROUP BY "+fromat+")a LEFT JOIN (\n" +
                "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS createTime,id from lora_alarm_list \n" +
                "WHERE ep_id='ffffff100000f110' "+sqlwhere+
                " GROUP BY "+fromat+" ) \n" +
                "b on a.createTime=b.createTime\n) b on a.createTime=b.createTime " +
                "LEFT JOIN ( " +
                "SELECT "+fromat+" AS createTime,id FROM lora_alarm_list " +
                "    WHERE 1 = 1 AND ep_id IN ('ffffff100000f108','ffffff100000f109') " +
                "    "+sqlwhere+"  GROUP BY "+fromat+"" +
                ") c on c.createTime=a.createTime" +
                " LEFT JOIN (SELECT  " +
                "if(sum(if(di3=0,1,0))>0,'√','x') as di3 , "+fromat+" as createTime  " +
                "from lora_switching_value  " +
                " where 1=1 "+sqlwhere+" and equipmentid ='ffffff100000f10f' GROUP BY "+fromat+") d on d.createTime=a.createTime " +
                "LEFT JOIN (SELECT    "+fromat+" as createTime ,id  from lora_alarm_list  where 1=1  and ep_id in('ffffff100000f0f9')\n" +
                " GROUP BY "+fromat+") f on f.createTime=a.createTime " +
                "LEFT JOIN ( " +
                "SELECT   "+fromat+" as createTime ,id   from lora_alarm_list  where 1=1  and ep_id in('ffffff100000f0f8')  "+sqlwhere+" " +
                " GROUP BY "+fromat+" ) e on e.createTime=a.createTime " +
                "LEFT JOIN ( SELECT  "+fromat+" as createTime ,id  from lora_alarm_list  where 1=1 " + sqlwhere+
                " GROUP BY "+fromat+" ) h on h.createTime=a.createTime " +
                "LEFT JOIN ( " +
                "SELECT  if(sum(if(alarm_flag='1',1,0))>0,'x','√') as xingfeng  , "+fromat+" as createTime " +
                "from fresh_air_ups where 1=1 "+sqlwhere+" " +
                " GROUP BY "+fromat+") i on i.createTime=a.createTime " +
                "LEFT JOIN ( " +
                "SELECT  "+fromat+" AS createTime,id FROM " +
                " `lora_alarm_list` where 1=1  and ep_id in('93ce2e63d4ef266b') "+sqlwhere+" " +
                " GROUP BY "+fromat+") j on j.createTime=a.createTime " +
                "LEFT JOIN ( " +
                "SELECT "+fromat+" AS createTime,id FROM\n" +
                " `lora_alarm_list` where 1=1  and ep_id in('4f33c4d427cb1aac','ffffff100000c6fc')  "+sqlwhere+" " +
                " GROUP BY "+fromat+") k on k.createTime=a.createTime " +
                "LEFT JOIN ( " +
                "SELECT  if(sum(if(alarm_flag='1',1,0))>0,'x','√') as yangan  , "+fromat+" as createTime " +
                "from wireless_smoke_feel where 1=1 "+sqlwhere+" " +
                " GROUP BY "+fromat+") l on l.createTime=a.createTime " +
                "LEFT JOIN ( " +
                "SELECT  "+fromat+" as createTime," +
                " if(count(1)>0,'x','√') sxt " +
                "FROM `lora_alarm_list` where field_title='摄像头' " +
                " GROUP BY "+ fromat+") m on m.createTime=a.createTime "+
                "  ORDER BY createTime desc" ;
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        result.setRows(maps);
        return result;
    }
    @RequestMapping(value = "listAll")
    public String equipentError(HttpServletRequest request,String eId,Model model){

        String eqid= request.getParameter("eqid");
        String type= request.getParameter("eId");
        String typev= request.getParameter("type");
        if(type.length()>2){
            List<Map<String,Object>> list = listAll( request, eId);
            model.addAttribute("list",list);
            if(list.size()>0){
                model.addAttribute("map",list.get(0));
            }
        }else {
            List<Map<String,Object>> list = listbaojin( request, eId,eqid,typev);
            model.addAttribute("list",list);
            if(list.size()>0){
                model.addAttribute("map",list.get(0));
            }
        }
        return "/manage/report/equipentError";
    }

    public List<Map<String,Object>> listbaojin(HttpServletRequest request,String type,String eqid,String typev){

       String createTime= request.getParameter("createTime");
        String fromat="";
        if(StringUtils.isNotBlank(eqid)){
            fromat+=" and ep_id in("+eqid+") ";
        }
        if(!StringUtils.isBlank(type)){
            if("9".equals(type)){
                if (typev.equals("1")) {
                    fromat += " and DATE_FORMAT(create_time,'%Y-%m-%d') = '" + createTime + "' ";
                } else if (typev.equals("2")) {
                    fromat += " and DATE_FORMAT(create_time,'%Y-%m') =  '" + createTime + "'  ";
                } else if (typev.equals("3")) {
                    fromat += " and DATE_FORMAT(create_time,'%x年%v周') =  '" + createTime + "'  ";
                } else {
                    fromat += " and DATE_FORMAT(create_time,'%Y') = '" + createTime + "'  ";
                }
            }else {
                if (type.equals("1")) {
                    fromat += " and DATE_FORMAT(create_time,'%Y-%m-%d') = '" + createTime + "' ";
                } else if (type.equals("2")) {
                    fromat += " and DATE_FORMAT(create_time,'%Y-%m') =  '" + createTime + "'  ";
                } else if (type.equals("3")) {
                    fromat += " and DATE_FORMAT(create_time,'%x年%v周') =  '" + createTime + "'  ";
                } else {
                    fromat += " and DATE_FORMAT(create_time,'%Y') = '" + createTime + "'  ";
                }
            }
        }
        List<Map<String,Object>> list=jdbcTemplate.queryForList("SELECT l.create_time as 时间,alarm_info as 报警原因,  e.position as  设备名 " +
                " FROM `lora_alarm_list` l LEFT JOIN equipent e on l.ep_id=e.eid where 1=1 "+fromat);
        if(!"9".equals(type)){
            List<Map<String,Object>> list1=jdbcTemplate.queryForList("SELECT create_time as createTime,equipmentid from air_conditioning_ups where airconditioningrunstate in (0,1,2) "+fromat);
            for(Map<String,Object> m:list1){
                Map<String,Object> ms=new HashMap<>();
                String equipmentid=(String)m.get("equipmentid");
                if("ffffff100000f110".equals(equipmentid)){
                    ms.put("设备名","空调1");
                    ms.put("报警原因","空调1关机");
                    ms.put("时间",m.get("createTime"));
                }else if("ffffff100000f10d".equals(equipmentid)) {
                    ms.put("设备名","空调2");
                    ms.put("报警原因","空调2关机");
                    ms.put("时间",m.get("createTime"));
                }
                list.add(ms);

            }
        }


        return list;
    }
    public List<Map<String,Object>> listAll(HttpServletRequest request,String eId){
        Map<String, Object> map = Maps.newHashMap();
        String type1= request.getParameter("type");

        String createTime= request.getParameter("createTime");
        map.put("eid", eId);
        List<Map<String,Object>> list=new ArrayList<>();
        String sqlwhere="";
        List<Map<String, Object>> query = equipmentService.query(map);
        if(type1.equals("1")){
            sqlwhere+=" and DATE_FORMAT(create_time,'%Y-%m-%d') ='"+createTime+"' ";
        }else if(type1.equals("2")){
            sqlwhere+=" and DATE_FORMAT(create_time,'%Y-%m') = '"+createTime+"' ";
        }else if(type1.equals("3")){
            sqlwhere+=" and DATE_FORMAT(create_time,'%年%周') ='"+createTime+"' ";
        } else {
            sqlwhere+=" and DATE_FORMAT(create_time,'%Y') = '"+createTime+"' ";
        }
        Object obj = null;
        if(!query.isEmpty()) {
            String type = (String) query.get(0).get("type");
            EquipmentEnum equipmentEnum = EquipmentEnum.getEquipmentByType(type);
            Map<String, Boolean> sort = Maps.newHashMap();
            sort.put("id", false);
            Map<String, Object> search = Servlets.getParametersStartingWith(request, "search_");
            search.put("EQ_equipmentid", eId);
           if (EquipmentEnum.ELECTRICITY_METER.getValue().equals(equipmentEnum.getValue())) {
               String sql="SELECT\n" +
                       "create_time as 时间,\n" +
                       "a_voltage as A相电压,\n" +
                       "b_voltage as B相电压,\n" +
                       "c_voltage as C相电压,\n" +
                       "a_current as A相电流,\n" +
                       "b_current as B相电流,\n" +
                       "c_current as C相电流,\n" +
                       "a_available_power as A相有功功率,\n" +
                       "b_available_power as B相有功功率,\n" +
                       "c_available_power as C相有功功率,\n" +
                       "rate as 频率\n" +
                       " from lora_electricity_meter a where 1=1 and equipmentid='"+eId+"' and (( a_voltage<210 or a_voltage>250) or(b_voltage<210 or b_voltage>250) \n" +
                       "or(c_voltage<210 or c_voltage>250) or(a_current<1 or a_current>20) or(b_current<1 or b_current>20) or(c_current<1 or c_current>20)\n" +
                       " or(a_available_power<90) or(b_available_power<90) or(c_available_power<90)or(rate<45 or rate>55)) "+sqlwhere;
               list=jdbcTemplate.queryForList("SELECT\n" +
                       "create_time as 时间,\n" +
                       "a_voltage as A相电压,\n" +
                       "b_voltage as B相电压,\n" +
                       "c_voltage as C相电压,\n" +
                       "a_current as A相电流,\n" +
                       "b_current as B相电流,\n" +
                       "c_current as C相电流,\n" +
                       "a_available_power as A相有功功率,\n" +
                       "b_available_power as B相有功功率,\n" +
                       "c_available_power as C相有功功率,\n" +
                       "rate as 频率\n" +
                       " from lora_electricity_meter a where 1=1 and equipmentid='"+eId+"' and (( a_voltage<210 or a_voltage>250) or(b_voltage<210 or b_voltage>250) \n" +
                       "or(c_voltage<210 or c_voltage>250) or(a_current<1 or a_current>20) or(b_current<1 or b_current>20) or(c_current<1 or c_current>20)\n" +
                       " or(a_available_power<90) or(b_available_power<90) or(c_available_power<90)or(rate<45 or rate>55)) "+sqlwhere);

           } else if (EquipmentEnum.UPS.getValue().equals(equipmentEnum.getValue())) {
               String sql="SELECT \n" +
                       "create_time as 时间,\n" +
                       "ac_voltagea as 交流输入电压A,\n" +
                       "ac_voltageb as 交流输入电压B, \n" +
                       "ac_voltagec as 交流输入电压C,\n" +
                       "ac_in_currenta as 交流输入电流A, \n" +
                       "ac_in_currentb as 交流输入电流B, \n" +
                       "ac_in_currentc as 交流输入电流C,\n" +
                       "ac_out_voltagea as 交流输出电压A, \n" +
                       "ac_out_voltageb as 交流输出电压B,\n" +
                       "ac_out_voltagec as 交流输出电压C,\n" +
                       "ac_out_currenta as 交流输出电流A, \n" +
                       "ac_out_currentb as 交流输出电流B,\n" +
                       "ac_out_currentc as 交流输出电流C,\n" +
                       "out_look_powera as 输出视在功率A,\n" +
                       "out_look_powerb as 输出视在功率B,\n" +
                       "out_look_powerc as 输出视在功率C,\n" +
                       "positive_battery_voltage as 正电池组电压, \n" +
                       "battery_temperature as 电池温度, \n" +
                       "battery_remain_capacity as 电池剩余时间 \n" +
                       "from lora_ups a where 1=1 and equipmentid='"+eId+"'  and  (( ac_voltagea<210 or ac_voltagea>250) or( ac_voltageb<210 or ac_voltageb>250) or( ac_voltagec<210 or ac_voltagec>250)\n" +
                       " or ( ac_out_voltagea<210 or ac_out_voltagea>250) or( ac_out_voltageb<210 or ac_out_voltageb>250) or( ac_out_voltagec<210 or ac_out_voltagec>250)\n" +
                       " or( ac_in_currenta>20 or ac_in_currenta<1) or( ac_in_currentb>20 or ac_in_currentb<1)or( ac_in_currentc>20 or ac_in_currentc<1)\n" +
                       " or( ac_out_currenta>20 or ac_out_currenta<1) or( ac_out_currentb>20 or ac_out_currentb<1)or( ac_out_currentc>20 or ac_out_currentc<1)\n" +
                       " or( out_look_powera<0.1 or out_look_powera>2) or( out_look_powerb<0.1 or out_look_powerb>2)and( out_look_powerc<0.1 or out_look_powerc>2)\n" +
                       " or( positive_battery_voltage>250 or positive_battery_voltage<210) or(battery_temperature>28 or battery_temperature<24)\n" +
                       " or(battery_remain_capacity<100)) "+sqlwhere;
               list= jdbcTemplate.queryForList("SELECT \n" +
                        "create_time as 时间,\n" +
                        "ac_voltagea as 交流输入电压A,\n" +
                        "ac_voltageb as 交流输入电压B, \n" +
                        "ac_voltagec as 交流输入电压C,\n" +
                        "ac_in_currenta as 交流输入电流A, \n" +
                        "ac_in_currentb as 交流输入电流B, \n" +
                        "ac_in_currentc as 交流输入电流C,\n" +
                        "ac_out_voltagea as 交流输出电压A, \n" +
                        "ac_out_voltageb as 交流输出电压B,\n" +
                        "ac_out_voltagec as 交流输出电压C,\n" +
                        "ac_out_currenta as 交流输出电流A, \n" +
                        "ac_out_currentb as 交流输出电流B,\n" +
                        "ac_out_currentc as 交流输出电流C,\n" +
                        "out_look_powera as 输出视在功率A,\n" +
                        "out_look_powerb as 输出视在功率B,\n" +
                        "out_look_powerc as 输出视在功率C,\n" +
                        "positive_battery_voltage as 正电池组电压, \n" +
                        "battery_temperature as 电池温度, \n" +
                        "battery_remain_capacity as 电池剩余时间 \n" +
                        "from lora_ups a where 1=1 and equipmentid='"+eId+"'  and  (( ac_voltagea<210 or ac_voltagea>250) or( ac_voltageb<210 or ac_voltageb>250) or( ac_voltagec<210 or ac_voltagec>250)\n" +
                        " or ( ac_out_voltagea<210 or ac_out_voltagea>250) or( ac_out_voltageb<210 or ac_out_voltageb>250) or( ac_out_voltagec<210 or ac_out_voltagec>250)\n" +
                        " or( ac_in_currenta>20 or ac_in_currenta<1) or( ac_in_currentb>20 or ac_in_currentb<1)or( ac_in_currentc>20 or ac_in_currentc<1)\n" +
                        " or( ac_out_currenta>20 or ac_out_currenta<1) or( ac_out_currentb>20 or ac_out_currentb<1)or( ac_out_currentc>20 or ac_out_currentc<1)\n" +
                        " or( out_look_powera<0.1 or out_look_powera>2) or( out_look_powerb<0.1 or out_look_powerb>2)and( out_look_powerc<0.1 or out_look_powerc>2)\n" +
                        " or( positive_battery_voltage>250 or positive_battery_voltage<210) or(battery_temperature>28 or battery_temperature<24)\n" +
                        " or(battery_remain_capacity<100)) "+sqlwhere);
            }
        }
        return list;
    }

    @RequestMapping(value = "showdetal")
    public String showdetal(HttpServletRequest request,Model model){
        String eqid= request.getParameter("eqid");
        String createTime= request.getParameter("createTime");
        String timesql="";
        if(createTime.length()==10){
            timesql+=" and DATE_FORMAT(create_time,'%Y-%m-%d') = '"+createTime+"' ";
        }else if(createTime.length()==7){
            timesql+=" and DATE_FORMAT(create_time,'%Y-%m') =  '"+createTime+"'  ";
        }else {
            timesql+=" and DATE_FORMAT(create_time,'%Y') = '"+createTime+"'  ";
        }
        Map<String,String> map=new HashMap<>();
        map.put("时间","");
        map.put("报警原因","");
        List<Map<String, Object>> list=new ArrayList<>();
        if(eqid.equals("0")){
            map.put("设备名","");
            list = getwsdAlamInfo(timesql);
        }else{
            list = getAlamInfo(eqid,timesql);
        }
        model.addAttribute("map",map);
        if(!list.isEmpty()){
            model.addAttribute("list",list);
        }
        return "/manage/report/equipentError";
    }
    private List<Map<String,Object>> getAlamInfo(String eid,String timesql){
        String sql="select\n" +
                "create_time,\n" +
                "alarm_info\n" +
                "from lora_alarm_list\n" +
                "where\n" +
                "ep_id = '"+eid+"'\n" +
                timesql;
        List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
        return list;
    }

    private List<Map<String,Object>> getwsdAlamInfo(String timesql){
        String sql="SELECT\n" +
                "* \n" +
                "FROM\n" +
                "(\n" +
                "SELECT\n" +
                "\ta.create_time,\n" +
                "\te.position, \n" +
                "\ta.alarm_info\n" +
                "FROM\n" +
                "\tlora_alarm_list a\n" +
                "\tINNER JOIN equipent e ON a.ep_id = e.eid \n" +
                "\tAND e.type = 2 \n" +
                ") d \n" +
                "WHERE 1=1 "+timesql;
        List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
        return list;
    }

    private String getNowDate(){
        Date time=new Date();
        int year = time.getYear() + 1900;
        int month = time.getMonth()+1;
        String monthstr=month>10?month+"":"0"+month;
        String date = time.getDate()>10?time.getDate()+"":"0"+time.getDate();
        return year+"-"+monthstr+"-"+date;
    }

    private String getNowMonth(){
        Date time=new Date();
        int year = time.getYear() + 1900;
        int month = time.getMonth()+1;
        String monthstr=month>10?month+"":"0"+month;
        return year+"-"+monthstr;
    }

}
